Param(
[string]$server='FIBICH-ACER\SQLEXPRESS',
[string]$db='CensusData',
[string]$userName="",
[string]$password=""
)

#----------------------------
# execute-scriptUnValidated()			
#----------------------------
function execute-scriptUnValidated ($scriptName) {
	if ($userName -eq "") {
		Invoke-SQLcmd -InputFile $scriptName -Database "$db" -ServerInstance "$server" -QueryTimeOut 3600;
	}else{
		Invoke-SQLcmd -InputFile $scriptName -Database "$db" -ServerInstance "$server" -QueryTimeOut 3600 -Username $userName -Password $password;
	}
};

#------------------------
# execute-script()			
#------------------------
function execute-script ($scriptName) {
	$queryString="Select 1 from dbo.database_version_information where script_name=replace(`'$scriptName`','Undo_','') and undone=0;";
if ($userName -eq "") {
	if ((Invoke-SQLcmd -Query $QueryString -Database "$db" -ServerInstance "$server" -QueryTimeOut 3600 |measure-object).count -eq 0) {
		write-host -ForegroundColor Cyan "Executing script $ScriptName(DB:$db)" -NoNewLine;
		Invoke-SQLcmd -InputFile $scriptName -Database "$db" -ServerInstance "$server" -QueryTimeOut 3600
		write-host -ForegroundColor Cyan "....Complete";
	}
	else {
		write-host -ForegroundColor Cyan "$ScriptName - Script has already been executed, it will be skipped ($db)";
	}
}else {
	if ((Invoke-SQLcmd -Query $QueryString -Database "$db" -ServerInstance "$server" -QueryTimeOut 3600 -Username $userName -Password $password |measure-object).count -eq 0) {
		write-host -ForegroundColor Cyan "Executing script $ScriptName(DB:$db)" -NoNewLine;
		Invoke-SQLcmd -InputFile $scriptName -Database "$db" -ServerInstance "$server" -QueryTimeOut 3600 -Username $userName -Password $password;
		write-host -ForegroundColor Cyan "....Complete";
	}
	else {
		write-host -ForegroundColor Cyan "$ScriptName - Script has already been executed, it will be skipped ($db)";
	}
}
};

#------------------------
# execute-SQLCmd()			
#------------------------
function BCP-Validation ($SQLText){
	if ($userName -eq "") {
		$resultSet=Invoke-SQLcmd -Query $SQLText -Database "$db" -ServerInstance "$server" -QueryTimeOut 3600;
		return($resultSet.Load_Successful_flag)
	}else{
		$resultSet=Invoke-SQLcmd -Query $SQLText -Database "$db" -ServerInstance "$server" -QueryTimeOut 3600 -Username $userName -Password $password;
		return($resultSet.Load_Successful_flag)
	}
	
};
#------------------------
# Error Action			
#------------------------
$ErrorActionPreference="STOP";

#------------------------
# Main()			
#------------------------
$ScriptPath=split-path $myInvocation.InvocationName
#start-transcript -path $scriptPath\Execute_US_Census_Data.log -force;
write-host 'This script is going to deploy the US Census Data project!' -foregroundcolor yellow;

#-------
#Just in case SQLPS Module invoke-sqlcmd leaves us out in its psdrive
#-------
$location=get-location;


#---------------
#Deploy Scripts
#---------------
execute-scriptUnValidated 'CensusData_V0R00000.sql'; #R1.0
execute-script 'CensusData_V0R00001.sql'; #R1.0
execute-script 'CensusData_V0R00002.sql'; #R1.0
execute-script 'CensusData_V0R00003.sql'; #R1.0
execute-script 'CensusData_V0R00004.sql'; #R1.0
execute-script 'CensusData_V0R00005.sql'; #R2.0
#---------------
#BCP Data #R2.0
#---------------
$sqlString="select lc.Load_Successful_flag from CensusData.dbo.ACSSF_Load_Control lc where lc.table_name='Geography_File' and lc.sample_set_code='2013e1';";
if ((BCP-Validation($sqlString)) -eq $False) { 
	& "$ScriptPath\BCP-inGeography.ps1" $Server $db $userName $password; 
}else {
	write-host -ForegroundColor Cyan "Skipping loader 'BCP-inGeography.ps1' table already loaded.($db)";
};
$sqlString="select lc.Load_Successful_flag from CensusData.dbo.ACSSF_Load_Control lc where lc.table_name='Summary_File_0001' and lc.sample_set_code='2013e1';";
if ((BCP-Validation($sqlString)) -eq $False) { 
	& "$ScriptPath\BCP-inACSSF0001.ps1" $Server $db $userName $password; 
}else {
	write-host -ForegroundColor Cyan "Skipping loader 'BCP-inACSSF0001.ps1' table already loaded.($db)";
};
#---------------
#Deploy Scripts #R2.0
#---------------
execute-script 'CensusData_V0R00006.sql'; #Validation Script #R2.0
execute-script 'CensusData_V0R00007.sql'; #R2.0
#---------------
#BCP Data #R2.0
#---------------
$sqlString="select lc.Load_Successful_flag from CensusData.dbo.ACSSF_Load_Control lc where lc.table_name='Geography_File' and lc.sample_set_code='2012e1';";
if ((BCP-Validation($sqlString)) -eq $False) { 
	& "$ScriptPath\BCP-in20121Geography.ps1" $Server $db $userName $password; 
}else {
	write-host -ForegroundColor Cyan "Skipping loader 'BCP-in20121Geography.ps1' table already loaded.($db)";
};
$sqlString="select lc.Load_Successful_flag from CensusData.dbo.ACSSF_Load_Control lc where lc.table_name='Summary_File_0001' and lc.sample_set_code='2012e1';";
if ((BCP-Validation($sqlString)) -eq $False) { 
	& "$ScriptPath\BCP-in20121ACSSF0001.ps1" $Server $db $userName $password; 
}else {
	write-host -ForegroundColor Cyan "Skipping loader 'BCP-in20121ACSSF0001.ps1' table already loaded.($db)";
};
#---------------
#Deploy Scripts #R2.0
#---------------
execute-script 'CensusData_V0R00008.sql'; #R2.0
execute-script 'CensusData_V0R00009.sql'; #Validation Script R2.0 
#---------------
#BCP Data #R2.0
#---------------
$sqlString="select lc.Load_Successful_flag from CensusData.dbo.ACSSF_Load_Control lc where lc.table_name='Summary_File_0002' and lc.sample_set_code='2013e1';";
if ((BCP-Validation($sqlString)) -eq $False) { 
	& "$ScriptPath\BCP-inACSSF0002.ps1" $Server $db $userName $password; 
}else {
	write-host -ForegroundColor Cyan "Skipping loader 'BCP-inACSSF0002.ps1' table already loaded.($db)";
};
$sqlString="select lc.Load_Successful_flag from CensusData.dbo.ACSSF_Load_Control lc where lc.table_name='Summary_File_0002' and lc.sample_set_code='2012e1';";
if ((BCP-Validation($sqlString)) -eq $False) { 
	& "$ScriptPath\BCP-in20121ACSSF0002.ps1" $Server $db $userName $password; 
}else {
	write-host -ForegroundColor Cyan "Skipping loader 'BCP-in20121ACSSF0002.ps1' table already loaded.($db)";
};
#-------
#Just in case SQLPS Module invoke-sqlcmd leaves us out in its psdrive
#-------
#stop-transcript;
set-location $location; 
